<?php
 
/**
 * @package model.dao
 * @author Roy & Sanu.S <sanu@micmacsolutions.com>
 * @author Modified By Roy & Sanu.S
 * @copyright Copyright &copy; 2010, Micmac Solutions
 * @since 1.0 - Dec-01-2010
 * @version 1.1
 */
require_once("ArrayCollection.php");
require_once 'vo/ImageVO.php';
require_once 'vo/MessageVO.php';
require_once 'vo/PartnerVO.php';
require_once 'vo/SubmissionVO.php';
require_once 'vo/WatchListVO.php';
require_once 'email/EmailService.php';

class VehicleDAOImpl
{
    /**
     * @access public
     * @staticvar Instance of MemberDAOImpl
     */
    public static $_instance    = null;
  
    /**
     * @access public
     */
    var $_mdb2                = null;
  
    /**
     * <p>Get an instance</p>
     *
     * @access public
     * 
     */
	
    public static function getInstance()
    {
    
        if(self::$_instance == null)
        {
            self::$_instance = new VehicleDAOImpl();
            date_default_timezone_set('America/Los_Angeles');
        }
      
        return self::$_instance;
    }
      
    
   

    public function deleteImage($del_id)
    {
		$selQuery = "SELECT * FROM vehicle_image WHERE image_id =".$del_id;
		$resultSelQuery = mysql_query($selQuery) or die("Query ERROR SELIMG");
		
		$del_img_row =  mysql_fetch_object($resultSelQuery);
		
		unlink($_SERVER['DOCUMENT_ROOT'].'/freecarbidsdemo/'.$del_img_row->thump_url); 
		unlink($_SERVER['DOCUMENT_ROOT'].'/freecarbidsdemo/'.$del_img_row->image_url); 
		unlink($_SERVER['DOCUMENT_ROOT'].'/freecarbidsdemo/'.$del_img_row->crop_url); 
		
		$delQuery = "DELETE FROM vehicle_image WHERE image_id =".$del_id;
		$resultDelQuery = mysql_query($delQuery) or die("Query ERROR DELIMG");
		
		return true;
	
	}

    /**
     * <p>To get the user start up data by the given user id</p>
     *
     * @access public
     * @global Array Configuration Array
     * @param Integer $userId The id of the User    
     * @return $userStartUp the UserSettingsVO  and MemberRegVO
     */
    public function getVehicleByPageIndex($request)
   {    
          session_start();
        $whereString =  $request->status;
	 $company_alias = $_SESSION["user"]->company_alias;	
        if(isset($request->tower_company))
              $tower_company = $request->tower_company;
			  
        $startIndex = $request->index;
        $endIndex = $request->num_record;
        $columsName = $request->criteria;
		
        if(!isset($request->type))
                $request->type = "";
                
        $searchString = "%".$request->search_key ."%";
      
         if(!$columsName)
            $columsName=null;
         
        if($columsName != null && $columsName != 'all')
        {
            
            if($columsName == 'vehicle_id')
            {   
                $columsName = "v.".$columsName;
                $search_statement = " AND ".$columsName." = '".$request->search_key."' AND (".$columsName." LIKE '".$searchString."')";
            }
            else
            {    $columsName = "v.".$columsName; 
                $search_statement = "AND (".$columsName." LIKE '".$searchString."')";
            }
        }
        else if($columsName == null )
        {
            $search_statement = '';
		}
		else if($columsName == 'all')
		{
				$search_statement = " AND ( v.vehicle_id LIKE '".$searchString."' OR v.year LIKE '".$searchString."' OR v.make LIKE '".$searchString."' OR v.model LIKE '".$searchString."' OR v.bid_price LIKE '".$searchString."' OR v.asking_price LIKE '".$searchString."' OR v.first_name LIKE '".$searchString."' OR v.phone LIKE '".$searchString."' OR v.city LIKE '".$searchString."' OR v.state LIKE '".$searchString."') ";
		}
        else if($request->search_key == null)
        {
            $search_statement = "";
        }
       
      
        if($request->sort_string != '')
        {
            $current_sort_string = $request->sort_string;
        }
        else
        {
            $current_sort_string = 'vc.update_date';
        }
      
        if($request->sort_type == 0)
        {
            $current_sort_type = 'DESC';
        }
        else
        {
            $current_sort_type = 'ASC';
        }
		
		$company = ', c.company_name ';
		$orderBy = "ORDER BY ".$current_sort_string." ".$current_sort_type;
		
        if($request->sort_string == 'message_notifier')
        {
           $company = '';
			$orderBy = '';
			$table_string = "(SELECT DISTINCT (t.vehicle_id), t.year, t.make, t.model, t.vin, t.mileage, t.engine, t.transmission, t.drivetrain_type, t.title_note, t.title_name, t.ad_source, t.color, t.wrong, t.great, t.i_want_to, t.bid_price, t.asking_price, t.accepted_price, t.removal_date, t.first_name, t.email, t.phone, t.cell_phone, t.contact_me, t.located_at, t.paper_work, t.street_address, t.city, t.state, t.zip, t.v_street_address, t.v_city, t.v_state, t.v_zip, t.contact_name, t.bussiness_name, t.contact_phone, t.delivery, t.delivery_note, t.delivery_option, t.private_note, t.contact_note, t.is_watch, t.is_read, t.status, t.current_action, t.is_locked, t.locked_by, t.locked_id, t.assist_by, t.locked_date, t.update_date, t.date, t.is_partner,t.company_name,veh_map.role_id
                                         FROM (SELECT(v.vehicle_id), v.year, v.make, v.model, v.vin, v.mileage, v.engine, v.transmission, v.drivetrain_type, v.title_note, v.title_name, v.ad_source, v.color, v.wrong, v.great, v.i_want_to, v.bid_price, v.asking_price, v.accepted_price, v.removal_date, v.first_name, v.email, v.phone, v.cell_phone, v.contact_me, v.located_at, v.paper_work, v.street_address, v.city, v.state, v.zip, v.v_street_address, v.v_city, v.v_state, v.v_zip, v.contact_name, v.bussiness_name, v.contact_phone, v.delivery, v.delivery_note, v.delivery_option, v.private_note, v.contact_note, v.is_watch, v.is_read, v.status, v.current_action, v.is_locked, v.locked_by, v.locked_id, v.assist_by, v.locked_date, v.update_date, v.date, v.is_partner, IF( m.is_read =0, 0, 1 ) AS m_read, c.company_name
                                         FROM vehicle v INNER JOIN ".$company_alias."_vehicle veh_map ON v.vehicle_id=veh_map.vehicle_id LEFT JOIN message m ON v.vehicle_id = m.vehicle_id LEFT JOIN company c ON v.delivery = c.id";
       
	   }
        else if($request->type == "tower")
        {
            $table_string = " vehicle v INNER JOIN ".$company_alias."_vehicle veh_map ON v.vehicle_id=veh_map.vehicle_id LEFT JOIN vehicle_tow_status vts ON v.vehicle_id=vts.vehicle_id LEFT JOIN company c ON v.delivery = c.id LEFT JOIN vehicle_status vstatus ON vts.tow_status=vstatus.status_id LEFT JOIN vehicle_make ON v.make=vehicle_make .make_id LEFT JOIN states s ON v.state=s .state_code LEFT JOIN states vs ON v.v_state=vs .state_code LEFT JOIN vehicle_model ON v.model=vehicle_model .model_id";
        }
		else
        {
            $table_string = " vehicle v INNER JOIN ".$company_alias."_vehicle veh_map ON v.vehicle_id=veh_map.vehicle_id LEFT JOIN company c ON v.delivery = c.id  LEFT JOIN vehicle_make ON v.make=vehicle_make .make_id LEFT JOIN states s ON v.state=s .state_code LEFT JOIN states vs ON v.v_state=vs .state_code  LEFT JOIN vehicle_model ON v.model=vehicle_model .model_id";
        }
		
        if($request->type == "tower")
        {
            if($whereString == 'All')
            {
                $whereString = " IN(1,2,3,5,6,7)";
				
               $whereComboString="WHERE tow_status'".$whereString."' ".$search_statement." ORDER BY assigned_date LIMIT " .$startIndex."," .$endIndex; 
            }   
            else
                 $whereString = "=" .$whereString;
               $whereComboString="WHERE tow_status".$whereString." ".$search_statement." ORDER BY assigned_date LIMIT " .$startIndex."," .$endIndex;
        }
        else 
        {
            if($whereString == 'All')
            {
            $whereString = "!='" .$whereString;
			//$whereString = "%%";
			
            $whereComboString = "WHERE status ".$whereString."'".$search_statement."ORDER BY m_read, v.update_date DESC)t ) b  LIMIT " .$startIndex."," .$endIndex; 
            }
            else
            {
            $whereString = "='" .$whereString;
            $whereComboString = "WHERE status ".$whereString."'".$search_statement."ORDER BY m_read, v.update_date DESC)t ) b  LIMIT " .$startIndex."," .$endIndex; 
           }
        }
        if($request->type == "tower")
        {
            $query_data = "SELECT v.*,vehicle_make.make  as make_name ,s.state  as state_name,vs.state as v_state_name,vehicle_model.model_name " .$company." ".", vts.tow_status, vstatus.status_name, vts.tow_is_read, vts.assigned_date, veh_map.role_id FROM".$table_string." ".$whereComboString;
        }
		else
		{
			  if($request->sort_string != 'message_notifier')
			  {
			 $query_data = "SELECT v.*,vehicle_make.make  as make_name ,s.state  as state_name,vs.state as v_state_name, vehicle_model.model_name ".$company.", veh_map.role_id FROM".$table_string." WHERE status ".$whereString."'".$search_statement.$orderBy." LIMIT ".$startIndex."," .$endIndex; 
			  }
			  else
			  {
				 $query_data = "SELECT *".$company." FROM".$table_string." WHERE status ".$whereString."'".$search_statement." ORDER BY m_read, v.update_date DESC)t ) b LIMIT ".$startIndex."," .$endIndex;      
			  }                           
                        
		}
	
        $result_data = mysql_query($query_data) or die("Query ERROR12".$query_data);
        $arrayCollection = new ArrayCollection();
        $tmps[] = '';
        $i = 0;
       
        while ($row =  mysql_fetch_object($result_data))
        {
            $tmps[$i] =    self::createVehicleObject($row);
            $i++;
        }
        $arrayCollection->source = $tmps;
        $vehicleRequestVO = new VehicleRequestVO();
        $vehicleRequestVO->seller = $arrayCollection;
        $vehicleRequestVO->index = $request->index;
        $vehicleRequestVO->num_record  = $request->num_record ;
        $vehicleRequestVO->status = $request->status;
        if($request->type == "tower")
        {
            
            $count_result = mysql_query("SELECT count(*) AS total FROM".$table_string." WHERE delivery='".$tower_company."' AND tow_status".$whereString." ".$search_statement);
            $count_row = mysql_fetch_array($count_result);
            $vehicleRequestVO->vehicle_count = $count_row['total'];
        }
        else 
        {
            
                
          if($request->sort_string != 'message_notifier')
            {
                $query = "SELECT count(*) AS total FROM".$table_string." WHERE status ".$whereString."'".$search_statement; 
                $count_result = mysql_query($query) or die("Error".$query);
			//return "SELECT count(*) AS total FROM".$table_string." WHERE status".$whereString."'".$search_statement;
                $count_row = mysql_fetch_array($count_result);
                $query = "SELECT count(*) AS car_count FROM".$table_string." WHERE status".$whereString."' AND v.is_read=0 ".$search_statement;
                $newCars_result = mysql_query($query) or die("Error".$query);
                $newCars_row = mysql_fetch_array($newCars_result);
                
                $avg_query = "select SUM( asking_price ) AS sum_asking, SUM( bid_price ) AS sum_bid, AVG(asking_price) AS avg_asking, AVG(bid_price) AS avg_bid, AVG(year) AS avg_year from (select @rownum:=@rownum+1 as rank, v.* from ".$table_string.",(SELECT @rownum:=0) r where status".$whereString."' ".$search_statement." order by ".$current_sort_string." ".$current_sort_type." ) t where rank>=".($startIndex + 1)." and rank<=".(($startIndex + $endIndex));
                $avg_result = mysql_query($avg_query) or die("Error".$avg_query);
                $avg_row = mysql_fetch_array($avg_result);

                $countmsg_result = "SELECT count(m.message_id) AS msg_count from ".$table_string.", message m where v.vehicle_id = m.vehicle_id AND m.is_read = 0 AND status".$whereString."'";
                $exe_query = mysql_query($countmsg_result) ;
                $countmsg_row = mysql_fetch_array($exe_query);				
            }
            else
            {
                $query = "SELECT count(*) AS total FROM".$table_string." WHERE status ".$whereString."'".$search_statement." ORDER BY m_read, v.update_date DESC)t )b"; 
                $count_result = mysql_query($query) or die("Error".$query);
			//return "SELECT count(*) AS total FROM".$table_string." WHERE status".$whereString."'".$search_statement;
                $count_row = mysql_fetch_array($count_result);
                $query = "SELECT count(*) AS car_count FROM".$table_string." WHERE status".$whereString."' AND v.is_read=0".$search_statement." ORDER BY m_read, v.update_date DESC)t ) b ";
                $newCars_result = mysql_query($query) or die("Error".$query);
                $newCars_row = mysql_fetch_array($newCars_result);
                
                  
                
            } 
			
            /* Total number of rows in the vehicle table */
            $vehicleRequestVO->vehicle_count = $count_row['total'];
            if($request->sort_string != 'message_notifier')
            {
                $vehicleRequestVO->avg_askPrice = $avg_row['avg_asking'];
                $vehicleRequestVO->avg_bidPrice = $avg_row['avg_bid'];
                $vehicleRequestVO->avg_year = $avg_row['avg_year'];
                $vehicleRequestVO->total_askPrice = $avg_row['sum_asking'];
                $vehicleRequestVO->total_bidPrice = $avg_row['sum_bid'];
                $vehicleRequestVO->unreadMsg = $countmsg_row['msg_count'];
            }
            $vehicleRequestVO->new_cars = $newCars_row['car_count'];          
        }  
        mysql_free_result($result_data);
        return $vehicleRequestVO;
    }
    
    public function getTodaysdelivering($company)
    {
      
        return mysql_query("SELECT vehicle.*, vehicle_tow_status.vtstatus_id, vehicle_status.status_name FROM vehicle LEFT JOIN vehicle_tow_status ON vehicle.vehicle_id=vehicle_tow_status.vehicle_id LEFT JOIN vehicle_status ON vehicle_tow_status.tow_status=vehicle_status.status_id WHERE delivery=".$company." AND DATE(removal_date)=DATE(NOW()) LIMIT 0,5");
    }
    
    public function getLastOpened($company)
    {
       return mysql_query("SELECT vehicle.vehicle_id,vehicle.year,vehicle.make,vehicle.model,vehicle.v_street_address,vehicle.v_city,vehicle.v_state, vehicle_status.status_name, vehicle_tow_status.tow_status, vehicle_tow_status.updated_date FROM vehicle LEFT JOIN vehicle_tow_status ON vehicle.vehicle_id=vehicle_tow_status.vehicle_id LEFT JOIN vehicle_status ON vehicle_tow_status.tow_status=vehicle_status.status_id WHERE delivery=".$company." AND DATE(vehicle_tow_status.updated_date)>0 ORDER BY vehicle_tow_status.updated_date DESC LIMIT 0,5");
    }
   
    public function setTowStatus($vehicle_id,$tow_status)
    {
        if(mysql_query("UPDATE vehicle_tow_status SET tow_status=".$tow_status.", updated_date=NOW() WHERE vehicle_id=".$vehicle_id))
            return true;
        else
            return false;
    }
    
    public function getTowVehicleCounts($tower_id)
    {
        return mysql_fetch_array( mysql_query("SELECT SUM(CASE WHEN tower_id=".$tower_id." THEN 1 ELSE 0 END) AS all_vehicle,
                                   SUM(CASE WHEN tower_id=".$tower_id." AND tow_status=1 THEN 1 ELSE 0 END) AS new_vehicle,
                                   SUM(CASE WHEN tower_id=".$tower_id." AND tow_status=2 THEN 1 ELSE 0 END) AS in_progress,
                                   SUM(CASE WHEN tower_id=".$tower_id." AND tow_status=3 THEN 1 ELSE 0 END) AS delivered
                                       FROM vehicle_tow_status"));
    }
  
    public function getVehicleCount()
    {
        $result[] = '';
      
        $ns_newCars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle` WHERE is_read=0 AND status='New Submission'");
        $ns_newCars_row = mysql_fetch_array($ns_newCars_result);
        $ba_newCars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle` WHERE is_read=0 AND status='Bid Accepted'");
        $ba_newCars_row = mysql_fetch_array($ba_newCars_result);
        $all_newCars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle` WHERE is_read=0");
        $all_newCars_row = mysql_fetch_array($all_newCars_result);
        $ns_countmsg_result = mysql_query("SELECT count( m.message_id ) as total FROM vehicle v, message m WHERE v.status='New Submission' AND v.vehicle_id = m.vehicle_id AND m.is_read =0");
        $ns_countmsg_row = mysql_fetch_array($ns_countmsg_result);
        $ba_countmsg_result = mysql_query("SELECT  count( m.message_id ) as total FROM vehicle v, message m WHERE v.status='Bid Accepted' AND v.vehicle_id = m.vehicle_id AND m.is_read =0");
        $ba_countmsg_row = mysql_fetch_array($ba_countmsg_result);
        $all_countmsg_result = mysql_query("SELECT  count( m.message_id ) as total FROM vehicle v, message m WHERE v.vehicle_id = m.vehicle_id AND m.is_read =0");
        $all_countmsg_row = mysql_fetch_array($all_countmsg_result);
        
		$cReminder = mysql_query("SELECT count( * ) as total_record  FROM `watchlist`");
        $cReminder_row= mysql_fetch_array($cReminder);
		
        $result[0] = $ns_newCars_row['total'];
        $result[1] = $ba_newCars_row['total'];
        $result[2] = $ns_countmsg_row['total'];
        $result[3] = $ba_countmsg_row['total'];
        $result[4] = $all_newCars_row['total'];
        $result[5] = $all_countmsg_row['total'];
		$result[6] = $cReminder_row['total_record'];
        return $result;
    }
  
    public function updateVehicleFieldByID($vehicleId, $fieldName, $value, $fieldName_1, $value_1, $admin)
    {
        $myDate = date('Y-m-d H:i:s');
        $c_vehicleId = $vehicleId;
        $adminUser = $admin;
        $c_fieldName = $fieldName;
        $c_value = $value;
        $c_fieldName_1 = $fieldName_1;
        $c_value_1 = $value_1;
      
        if($c_fieldName_1)
        {
            $query_insert =  "UPDATE vehicle SET " .$c_fieldName. "=" .$c_value.", ".$c_fieldName_1. "='" .$c_value_1."' WHERE vehicle_id=".$c_vehicleId;
            $result_insert = mysql_query($query_insert) or die("Query ERROR277");
        }
        else
        {
            if($fieldName == 'delivery')
            {
                /*$delivery_id = '';
                $delivery = mysql_fetch_array(mysql_query("SELECT id FROM company WHERE company_name=".$c_value));
                
                if(isset($delivery["id"]))
                    $delivery_id= $delivery["id"];*/
                $query_insert = "UPDATE vehicle SET " .$c_fieldName. "=(SELECT id FROM company WHERE company_name=".$c_value.") WHERE vehicle_id=".$c_vehicleId;
                $result_insert = mysql_query($query_insert) or die("Query ERROR367");
                mysql_query("UPDATE vehicle_tow_status SET tower_id=(SELECT id FROM company WHERE company_name=".$c_value."), updated_date=NOW() WHERE vehicle_id=".$c_vehicleId)or die("QUERY ERROR");
            }
            else{
                $query_insert = "UPDATE vehicle SET " .$c_fieldName. "=" .$c_value." WHERE vehicle_id=".$c_vehicleId;
                $result_insert = mysql_query($query_insert) or die("Query ERROR366");
            }
          
            if($c_fieldName == 'is_read')
            {
                $query_insert_1 =  "UPDATE message SET " .$c_fieldName. "=" .addslashes($c_value)." WHERE vehicle_id=".$c_vehicleId;
                $result_insert_1 = mysql_query($query_insert_1) or die("Query ERROR262");
            }
            else if ($c_fieldName == 'is_watch')
            {
                self::updateAssistName($adminUser, $c_vehicleId);
            }
        }
      
        $logDec = "Vehicle field updated".$c_fieldName;
        self::addNewLog($c_vehicleId, $logDec, "Vehicle Updated", $adminUser);
        return $result_insert;
    }
  
    public function getBidVehicleDetails($vehicle_key)
    {
        $query_bid = mysql_query("SELECT response_link.vehicle_id FROM response_link WHERE response_link.key='".$vehicle_key."'");
        $result_bid = mysql_fetch_array($query_bid);

        $vehicle_info = self::getVehicleByVehicleID($result_bid['vehicle_id']);
        return $vehicle_info;
    }
  
    public function getVehicleByVehicleID($vehicleId)
    {
        $query_data = "SELECT v.*,vehicle_make.make  as make_name ,s.state  as state_name,vs.state as v_state_name,vehicle_model.model_name ,vehicle_status.status_name, company.company_name, company.street as tower_street, company.city as tower_city, company.state as tower_state FROM vehicle as v  LEFT JOIN vehicle_make ON v.make=vehicle_make .make_id  LEFT JOIN vehicle_model ON v.model=vehicle_model .model_id LEFT JOIN company ON v.delivery=company.id LEFT JOIN vehicle_tow_status ON v.vehicle_id=vehicle_tow_status.vehicle_id LEFT JOIN vehicle_status ON vehicle_tow_status.tow_status=vehicle_status.status_id LEFT JOIN states s ON v.state=s .state_code LEFT JOIN states vs ON v.v_state=vs .state_code WHERE v.vehicle_id=".$vehicleId;
        $result_data = mysql_query($query_data) or die("Query ERROR241".$query_data);
      
        if($result_data == true)
        {       
             //mysql_query("UPDATE vehicle_tow_status SET tow_is_read=true, updated_date=NOW() WHERE vehicle_id=".$vehicleId);
            $row =  mysql_fetch_object($result_data);
            $vehicle = self::createVehicleObject($row);
        }
        return $vehicle;
    }
    
    public function getTowerVehicleByID($vehicleId,$tower_id)
    {
        $query_data = "SELECT v.vehicle_id,v.make,v.model, v.year, v.vin, v.mileage, v.title_note, v.title_name, v.color, v.wrong, v.great, v.bid_price, v.asking_price, v.accepted_price, v.removal_date, v.first_name, v.email, v.phone, v.cell_phone, v.street_address, v.city, v.state, v.zip, v.v_street_address, v.v_city, v.v_state, v.v_zip, v.contact_name, v.bussiness_name, v.contact_phone, v.delivery, v.delivery_note,v.private_note, v.contact_note, v.is_watch, v.is_read,  v.status, v.current_action, v.is_locked, v.locked_by, v.locked_id, v.assist_by, v.locked_date, v.update_date, v.date, v.is_partner,vehicle_make.make  as make_name,vehicle_model.model_name as model_name , vehicle_status.status_name, company.company_name, company.street as tower_street, company.city as tower_city, company.state as tower_state,asset_engine .asset_name as engine,asset_transmission .asset_name as transmission, asset_dri .asset_name as drivetrain_type, asset_ad .asset_name as ad_source,asset_want .asset_name as i_want_to, asset_con .asset_name as contact_me , asset_loc .asset_name as located_at, asset_pap .asset_name as paper_work , asset_del.asset_name as delivery_option FROM vehicle as v LEFT JOIN vehicle_make ON v.make=vehicle_make .make_id  LEFT JOIN vehicle_model ON v.model=vehicle_model .model_id  LEFT JOIN company ON v.delivery=company.id LEFT JOIN vehicle_tow_status ON v.vehicle_id=vehicle_tow_status.vehicle_id LEFT JOIN vehicle_status ON vehicle_tow_status.tow_status=vehicle_status.status_id LEFT JOIN assets AS asset_engine ON asset_engine .asset_id=v.engine LEFT JOIN assets AS asset_transmission ON asset_transmission .asset_id=v.transmission  LEFT JOIN assets AS asset_dri ON asset_dri .asset_id= v.drivetrain_type  LEFT JOIN assets AS asset_ad ON asset_ad .asset_id= v.ad_source LEFT JOIN assets AS asset_want ON asset_want .asset_id=  v.i_want_to LEFT JOIN assets AS asset_con ON asset_con .asset_id=  v.contact_me  LEFT JOIN assets AS asset_loc ON asset_loc .asset_id=  v.located_at LEFT JOIN assets AS asset_pap ON asset_pap .asset_id=  v.paper_work  LEFT JOIN  assets AS asset_del ON asset_del.asset_id=  v.delivery_option  WHERE v.vehicle_id=".$vehicleId." AND delivery=".$tower_id;
        $result_data = mysql_query($query_data) or die("Query ERROR241".$query_data);
        $vehicle=NUll;
        if(mysql_num_rows($result_data))
        {       
             mysql_query("UPDATE vehicle_tow_status SET tow_is_read=true, updated_date=NOW() WHERE vehicle_id=".$vehicleId);
            $row =  mysql_fetch_object($result_data);
            $vehicle = self::createVehicleObject($row);
        }
        return $vehicle;
    }        
  
    public function getAllImageByVehicleID($vehicleID)
    {
        $query_data = "SELECT * FROM vehicle_image WHERE vehicle_id='".$vehicleID."'" ;
        $result_data = mysql_query($query_data) or die("Query ERROR5");
        $arrayCollection = new ArrayCollection();
        $tmps = array();
        $i = 0;
      
        while ($row =  mysql_fetch_object($result_data))
        {
            $tmp = new ImageVO();
            $tmp->imageId = $row->image_id;
            $tmp->imageUrl = $row->image_url;
            $tmp->thumpUrl = $row->thump_url;
		$tmp->cropUrl = $row->crop_url;

            $tmps[$i] =    $tmp;
            $i++;
        }
        if(count($tmps) >0)
            $arrayCollection->source = $tmps;
        return $arrayCollection;
    }
  
    public function updateMessageVehicleID($messages, $colName, $replay, $doMail, $update)
    {
      
        $myDate = date('Y-m-d H:i:s');
        $adminUser = $messages->messageOwener; 
        $c_vehicleID = $messages->vehicleId;
        $colName_1 = 'update_date';
        $colName_2 = "current_action"; 
     
        $c_offer = $messages->offer;

        $is_read = 0;
     
        if($messages->userType == 'admin')
        {
         
            $is_read = 1;
            self::updateAssistName($adminUser, $c_vehicleID);
        }
        else
		{
			$content = '<strong>New comment added on this vehicle <br><br>
			On '.$myDate.' '.$messages->messageOwener.' wrote: <br>Message: </strong>'.$messages->message.'<br>';
			//self::updateNotificationSender($c_vehicleID, $content);
		} 
        $query_insert = "INSERT INTO message ( `message_id` , `messageOwener` , `message` ,`is_read`, `messageRecipient` , `vehicle_id` , `offer`, `date`) VALUES (NULL,'" .$adminUser. "','" .addslashes($messages->message). "'," .$is_read. ",'" .$messages->messageRecipient. "'," .$c_vehicleID. "," .$c_offer.",'" .$myDate."')";
     
        $result_data = mysql_query($query_insert) or die("Query ERROR6" . $query_insert );
     
        $lastInsertedID = mysql_insert_id();
     
     
     
        if($colName)
        {
         
            self::updateVehicleFieldByID($c_vehicleID, $colName, "'".$myDate."'", $colName_2, $replay, $adminUser);
        }
        else
        {
         
            self::addNewLog($c_vehicleID, "New Comment added", "New Comment", $adminUser);
        }
        if($doMail == 'true')
        {
            $query_data = "SELECT first_name FROM vehicle WHERE vehicle_id='".$c_vehicleID."'" ;
            $result_name = mysql_query($query_data) or die("Query ERROR7");
            $result_name= mysql_fetch_array($result_name);
            $firstName = $result_name['first_name'];
         
            if($messages->userType == 'partner')
            {
                $type = 'PartnerComment';
                $firstName = $messages->messageTo;
            }
            else
            {
                $type = 'BidComment';
            }
		if($messages->messageRecipient)
		{
            		self::emailSender($c_vehicleID, $messages->messageRecipient, $type, $adminUser, $firstName);
		}

        }

         
        self::updateVehicleFieldByID($c_vehicleID, $colName_1, "'".$myDate."'", $colName_2, $replay, $adminUser);
        return $result_data;

    }
  
    private function updateAssistName($adminUser, $c_vehicleID)
    {
        $lock_user = "UPDATE vehicle SET assist_by='".$adminUser."' WHERE vehicle_id=".$c_vehicleID ;
        $lock_user_result = mysql_query($lock_user) or die("Query ERROR11");
    }
  
    public function getAllMessageVehicleID($vehicleID)
    {
          $query_data = "SELECT * FROM message WHERE vehicle_id='".$vehicleID."' ORDER BY date DESC";

        $result_data = mysql_query($query_data) or die("Query ERROR7");
      
        $arrayCollection = new ArrayCollection();
        $tmps[] = '';
        $i = 0;
      
        while ($row =  mysql_fetch_object($result_data))
        {
            $tmp = new MessageVO();
            $tmp->vehicleId = $row->vehicle_id;
            $tmp->message = $row->message;
            $tmp->messageOwener = $row->messageOwener;
            $tmp->messageRecipient = $row->messageRecipient;
            $tmp->offer = $row->offer;
            $tmp->isRead = (bool) $row->is_read;
            $tmp->dateTime = $row->date;
            $tmps[$i] =    $tmp;
            $i++;
        }
        if(count($tmps) >0)
            $arrayCollection->source = $tmps;
      
        return $arrayCollection;
    }
  
    public function getAllPrivateNote($vehicleID)
    {
          $query_data = "SELECT * FROM private_notes WHERE vehicle_id='".$vehicleID."' ORDER BY date DESC";

        $result_data = mysql_query($query_data) or die("Query ERROR7");
      
        $arrayCollection = new ArrayCollection();
        $tmps = array();
        $i = 0;
      
        while ($row =  mysql_fetch_object($result_data))
        {
            $tmp = new MessageVO();
            $tmp->vehicleId = $row->vehicle_id;
            $tmp->message = $row->private_note;
            $tmp->messageOwener = $row->messageOwener;
            $tmp->dateTime = $row->date;
            $tmps[$i] =    $tmp;
            $i++;
        }
        if(count($tmps) >0)
            $arrayCollection->source = $tmps;
      
        return $arrayCollection;
    }
  
   public function addNewPrivateNote($messageVO)
    {
        $myDate = date('Y-m-d H:i:s');
        $query_insert = "INSERT INTO private_notes ( `note_id` , `vehicle_id`, `private_note`, `messageOwener`, `date`) VALUES (NULL," .$messageVO->vehicleId. ",'" .addslashes($messageVO->message). "','" .$messageVO->messageOwener. "','" .$myDate."')";
        $result_data = mysql_query($query_insert) or die("Query ERROR6" . $query_insert );
        $lastInsertedID = mysql_insert_id();
        return $lastInsertedID;
    }

  
    public function updateVehicleByVehicleID($vehicle, $isNormal)
    {
        $adminUser = $vehicle->adminUser->firstName;
        $myDate = date('Y-m-d H:i:s');
        $c_vehicle_id = $vehicle->vehicle_id;
      
        $check_query = "select * from vehicle where vehicle_id = $c_vehicle_id";
        $check_data = mysql_query($check_query) or die("Query ERROR7ss");
      
        $isUpdate = 'false';
        $row = mysql_fetch_object($check_data);
        if(($row->locked_id == $vehicle->adminUser->admin_id) || ($isNormal == 0))
        {
             $delivery_id ='';
             if(isset($vehicle->delivery))
                $delivery = mysql_fetch_object(mysql_query("SELECT id FROM company WHERE company_name='".$vehicle->delivery."'"));
          if(isset($delivery->id))
           $delivery_id= $delivery->id;
          
            $query_insert = "UPDATE vehicle SET year='" .$vehicle->year. "', make='" .addslashes($vehicle->make). "', model='" .addslashes($vehicle->model). "', vin='" .addslashes($vehicle->vin). "', mileage='" .addslashes($vehicle->mileage). "', engine='" .addslashes($vehicle->engine). "', transmission='" .addslashes($vehicle->transmission). "', drivetrain_type='" .addslashes($vehicle->drivetrain_type). "', title_note='" .addslashes($vehicle->title_note). "', title_name='" .addslashes($vehicle->title_name). "', ad_source='" .addslashes($vehicle->ad_source). "', color='" .addslashes($vehicle->color). "', wrong='" .addslashes($vehicle->wrong). "', great='" .addslashes($vehicle->great). "', i_want_to='" .addslashes($vehicle->i_want_to). "', bid_price=" .$vehicle->bid_price. ",asking_price=" .$vehicle->asking_price. ",accepted_price=" .$vehicle->accepted_price. ", removal_date='" .$vehicle->removal_date. "', first_name='" .addslashes($vehicle->first_name). "', email='" .addslashes($vehicle->email). "', phone='" .$vehicle->phone. "', cell_phone='" .$vehicle->cell_phone. "', contact_me='" .addslashes($vehicle->contact_me). "', located_at='" .addslashes($vehicle->located_at). "', paper_work='" .addslashes($vehicle->paper_work). "', street_address='" .addslashes($vehicle->street_address). "', city='" .addslashes($vehicle->city). "', state='" .addslashes($vehicle->state). "', zip='" .addslashes($vehicle->zip). "', v_street_address='" .addslashes($vehicle->v_street_address). "', v_city='" .addslashes($vehicle->v_city). "', v_state='" .addslashes($vehicle->v_state). "', v_zip='" .addslashes($vehicle->v_zip). "', contact_name='" .addslashes($vehicle->contact_name). "', bussiness_name='" .addslashes($vehicle->bussiness_name). "', contact_phone='" .addslashes($vehicle->contact_phone). "', delivery='" .$delivery_id. "', delivery_note='" .addslashes($vehicle->delivery_note). "', delivery_option='" .addslashes($vehicle->delivery_option). "',contact_note='" .addslashes($vehicle->contact_note). "', is_watch=" .$vehicle->is_watch. ", is_read=" .$vehicle->is_read. ", status='" .addslashes($vehicle->status). "', current_action='" .addslashes($vehicle->current_action). "', locked_by='" .addslashes($vehicle->locked_by). "',update_date='" .$myDate."' WHERE vehicle_id='".$vehicle->vehicle_id."'";
            $result_insert = mysql_query($query_insert) or die("Query ERROR8".$query_insert);
            mysql_query("UPDATE vehicle_tow_status SET tower_id='".$delivery_id."', updated_by=".$vehicle->adminUser->admin_id.", updated_date=NOW() WHERE vehicle_id=".$vehicle->vehicle_id)or die("QUERY ERROR");
            self::updateAssistName($adminUser, $c_vehicle_id);
            self::addNewLog($c_vehicle_id, "Vehicle updated", "New Submission", $adminUser);
          
            if($result_insert)
            {
                $isUpdate = 'true';
            }
            else
            {
                $isUpdate = 'false';
            }
        }
        else
        {
            $isUpdate = $row->locked_by;
        }
        return $isUpdate;
    }
  
    public function createNewVehicle($vehicle)
    {
          $myDate = date('Y-m-d H:i:s');
          $o_date = date('Y-m-d');
        if($vehicle->adminUser)
        {
            $adminUser = $vehicle->adminUser->firstName;
			$created_by = $vehicle->adminUser->firstName;
        }
        else
        {
            $adminUser = NULL;
			$created_by = $vehicle->firstName;
            $type = 'SellerSubmit';
        }
        
        if($adminUser)
        {
            $type = 'BuyerSubmit';  
        }
        else{
          
            $type = 'SellerSubmit';
        }
        
          $email = $vehicle->email;
          $vid = $vehicle->vehicle_id;
          $delivery_id ='';
          if($vehicle->delivery!='')
                $delivery = mysql_fetch_object(mysql_query("SELECT id FROM company WHERE company_name='".$vehicle->delivery."'"));
          if(isset($delivery->id))
           $delivery_id= $delivery->id;
          
          $query_insert = "INSERT INTO vehicle ( `vehicle_id` , `year` , `make` , `model` , `vin` , `mileage` , `engine` , `transmission` , `drivetrain_type` , `title_note` , `title_name` , `ad_source` , `color` , `wrong` , `great` , `i_want_to` , `bid_price` , `asking_price`, `accepted_price` , `removal_date` , `first_name` , `email` , `phone` , `cell_phone` , `contact_me` , `located_at` , `paper_work` , `street_address` , `city` , `state` , `zip` , `v_street_address` , `v_city` , `v_state` , `v_zip` , `contact_name` , `bussiness_name` , `contact_phone` , `delivery` , `delivery_note` , `delivery_option` , `private_note` , `contact_note` , `is_watch` , `is_read` , `status` , `current_action` ,`locked_by`,`locked_id`,`assist_by`,`locked_date`, `update_date` , `date`, `created_by`, `is_partner` ) VALUES (NULL,'".$vehicle->year. "','" .addslashes($vehicle->make). "','" .addslashes($vehicle->model). "','" .addslashes($vehicle->vin). "','" .addslashes($vehicle->mileage). "','" .addslashes($vehicle->engine). "','" .addslashes($vehicle->transmission). "','" .addslashes($vehicle->drivetrain_type). "','" .addslashes($vehicle->title_note). "','" .addslashes($vehicle->title_name). "','" .addslashes($vehicle->ad_source). "','" .addslashes($vehicle->color). "','" .addslashes($vehicle->wrong). "','" .addslashes($vehicle->great). "','" .addslashes($vehicle->i_want_to). "',".$vehicle->bid_price.",".$vehicle->asking_price.",".$vehicle->accepted_price.",'" .$vehicle->removal_date. "','" .addslashes($vehicle->first_name). "','" .addslashes($vehicle->email). "','" .$vehicle->phone. "','" .$vehicle->cell_phone. "','" .addslashes($vehicle->contact_me). "','" .addslashes($vehicle->located_at). "','" .addslashes($vehicle->paper_work). "','" .addslashes($vehicle->street_address). "','" .addslashes($vehicle->city). "','" .addslashes($vehicle->state). "','" .addslashes($vehicle->zip). "','" .addslashes($vehicle->v_street_address). "','" .addslashes($vehicle->v_city). "','" .addslashes($vehicle->v_state). "','" .addslashes($vehicle->v_zip). "','" .addslashes($vehicle->contact_name). "','" .addslashes($vehicle->bussiness_name). "','" .$vehicle->contact_phone. "','" . $delivery_id. "','" .addslashes($vehicle->delivery_note). "','" .addslashes($vehicle->delivery_option). "','" .addslashes($vehicle->private_note). "','" .addslashes($vehicle->contact_note). "'," .$vehicle->is_watch. ",".$vehicle->is_read.",'" .addslashes($vehicle->status). "','" .addslashes($vehicle->current_action). "','" .addslashes($vehicle->locked_by). "',0,'" .addslashes($adminUser). "','" .$myDate."','" .$myDate."','" .$myDate."','".$created_by."',".$vehicle->is_partner.")";
        $result_insert = mysql_query($query_insert) or die("Query ERROR9");
        $lastInsertedID = mysql_insert_id();
          if($result_insert)
            mysql_query("INSERT INTO vehicle_tow_status (vehicle_id,tower_id,tow_status,assigned_by,assigned_date) VALUES(".$lastInsertedID.", '" . $delivery_id. "', 1, ".$vehicle->adminUser->admin_id.", NOW())");
        if(count($vehicle->image_upload)>0)
        {
            for ($i=0; $i< count($vehicle->image_upload); $i++)
            {
                $image_insert = "UPDATE vehicle_image SET vehicle_id='".$lastInsertedID."' WHERE image_id='".$vehicle->image_upload[$i]."'";
                $image_insert = mysql_query($image_insert) or die("Query ERROR10");                  
            }
          
        }
      
        if(count($vehicle->private_note_array)>0)
        {
            for ($i=0; $i< count($vehicle->private_note_array); $i++)
            {
                $private_insert = "UPDATE private_notes SET vehicle_id='".$lastInsertedID."' WHERE note_id='".$vehicle->private_note_array[$i]."'";
                $private_insert_result = mysql_query($private_insert) or die("Query ERROR111");                  
            }
          
        }

        $submission_insert = "INSERT INTO submission_tracker (`vehicle_id`,`sub_ip`,`sub_platform`,`sub_device`,`gps_latitude`,`gps_longtitude`,`via_web_seller`,
        `via_web_buyer`,`via_mobile_seller`,`via_mobile_buyer`,`date`) VALUES (".$lastInsertedID.",'".$vehicle->sub_report->sub_ip."','".$vehicle->sub_report->sub_platform."','".$vehicle->sub_report->sub_device."','".$vehicle->sub_report->latitude."','".$vehicle->sub_report->longtitude."',".$vehicle->sub_report->via_w_seller.",".$vehicle->sub_report->via_w_buyer.",".$vehicle->sub_report->via_m_seller.",".$vehicle->sub_report->via_m_buyer.",'".$o_date."')";
        $submission_result = mysql_query($submission_insert) or die("Query ERROR111".$submission_insert);  

        self::addNewLog($lastInsertedID, "New Vehicle added", "New Submission", $adminUser);      
        //self::emailNotificationSender($vehicle, $type, $adminUser, $lastInsertedID);
		if($vehicle->email != '')
		{
			self::emailSender($lastInsertedID, $vehicle->email, $type, $adminUser, $vehicle->first_name);
		}
		
        return $lastInsertedID;
    }
  
    public function emailSender($id, $email, $type, $admin, $fname)
    {
        $myDate = date('Y-m-d H:i:s');
        $key = uniqid();
        $url = 'http://www.freecarbids.com/Autogatormediator.php?id='.$key.'&type=sel'  ;
        $email_insert = "INSERT INTO response_link ( `response_id` , `vehicle_id` ,`date_time`,`key` , `url` , `send_to`, `admin_name`) VALUES (NULL,".$id.",'".$myDate. "','" .$key. "','" .$url. "','" .$email."','" .$admin."')";
        $result_insert = mysql_query($email_insert) or die("Query ERROR11");
        $lastInsertedID = mysql_insert_id();
        $_instance = new TestEmail();
        $_instance->sendEmail($url, $type, $email, $id, $fname);
    }
	
	public function createDetailHtml($vehicle, $vid)
    {
		$html = '<table width="625" cellpadding="0" cellspacing="0">
		  <tr>
			<td height="20" colspan="2" bgcolor="#006600"><font color="#FFFFFF"><b>&nbsp;Bid#:'.$vid.'</b></font></td>
			<td height="20" colspan="2" bgcolor="#006600"></td>
		  </tr>
		  <tr>
			<td height="20" colspan="2" bgcolor="#006600"><font color="#FFFFFF"><b>&nbsp;Vehicle Information</b></font></td>
			<td height="20" colspan="2" bgcolor="#006600"><font color="#FFFFFF"><b>&nbsp;Seller Information</b></font></td>
		  </tr>
		  <tr>
			<td width="20%">&nbsp;Make - Model :</td>
			<td width="30%">&nbsp;'.$vehicle->make.' - '.$vehicle->model.'</td>
			<td width="20%">&nbsp;Name:</td>
			<td width="30%">&nbsp;'.$vehicle->first_name.'</td>
		  </tr>
		  <tr>
			<td width="20%" bgcolor="#E9FDE8">&nbsp;Year:</td>
			<td width="30%" bgcolor="#E9FDE8">&nbsp;'.$vehicle->year.'</td>
			<td width="20%" bgcolor="#E9FDE8">&nbsp;Address: </td>
			<td width="30%" bgcolor="#E9FDE8">&nbsp;'.$vehicle->street_address.'</td>
		  </tr>
		  <tr>
			<td width="20%">&nbsp;Mileage:</td>
			<td width="30%">&nbsp;'.$vehicle->mileage.'</td>
			<td width="20%">&nbsp;City - State &amp; Zip:</td>
			<td width="30%">&nbsp;'.$vehicle->city.' , '.$vehicle->state.' , '.$vehicle->zip.'</td>
		  </tr>
		  <tr>
			<td width="20%" bgcolor="#E9FDE8">&nbsp;I want to:</td>
			<td width="30%" bgcolor="#E9FDE8">&nbsp;'.$vehicle->i_want_to.'</td>
			<td width="20%" bgcolor="#E9FDE8">&nbsp;Phone:</td>
			<td bgcolor="#E9FDE8">&nbsp;'.$vehicle->phone.'</td>
		  </tr>
		  <tr>
			<td width="20%">&nbsp;Remove by:</td>
			<td width="30%">&nbsp;'.$vehicle->removal_date.'</td>
			<td width="20%">&nbsp;E-mail:</td>
			<td>&nbsp;&nbsp;'.$vehicle->email.'</td>
		  </tr>
		  <tr>
			<td width="20%" bgcolor="#E9FDE8">&nbsp;Asking Price:</td>
			<td width="30%" bgcolor="#E9FDE8">&nbsp;'.$vehicle->asking_price.'</td>
			<td width="20%" bgcolor="#E9FDE8">&nbsp;Contact Method :</td>
			<td width="30%" bgcolor="#E9FDE8">&nbsp;'.$vehicle->contact_me.'</td>
		  </tr>
		</table>';
		return $html;
	}
	
	public function updateNotificationSender($vid, $imgurl)
    {
		$ini_array = parse_ini_file("/../../config/configuration.ini","MAILCONF");	

		if($ini_array['MAILCONF']['MAIL_NOTIFICATION'] == 1)
		{
			$_instance = new TestEmail();		
			$_instance->sendUpdateNotification('SellerUpdateNotification', 'notifications@autogator.com', $vid, 'Autogator', $imgurl);
		}
    }
	
    public function emailNotificationSender($vehicle, $type, $admin, $lid)
    {
        $myDate = date('Y-m-d H:i:s');
        $key = uniqid();
        $url = 'http://www.freecarbids.com/AutogatorMediator.php?id='.$key.'&type=sel'  ;
        $email_insert = "INSERT INTO response_link ( `response_id` , `vehicle_id` ,`date_time`,`key` , `url` , `send_to`, `admin_name`) VALUES (NULL,". $lid.",'".$myDate. "','" .$key. "','" .$url. "','" .$vehicle->email."','" .$admin."')";
        $result_insert = mysql_query($email_insert) or die("Query ERROR11");
        $lastInsertedID = mysql_insert_id();
		$_instance = new TestEmail();
		if($vehicle->email)
		{
			$_instance->sendEmail($url, $type, $vehicle->email, $vehicle->vehicle_id, $vehicle->first_name);
		}
		$ini_array = parse_ini_file("/../../config/configuration.ini","MAILCONF");	

		if($ini_array['MAILCONF']['MAIL_NOTIFICATION'] == 1)
		{
			$subContent = "New - [".$vehicle->year."] ".$vehicle->make." - ".$vehicle->model. " | ".$vehicle->city."/".$vehicle->state;
			$detailHtml = self::createDetailHtml($vehicle, $lid);
			$_instance->sendNotificationEmail($url, 'SellerSubmitNotification', 'notifications@autogator.com', $lid, 'Autogator', $detailHtml, $subContent);
		}
    }
  
    public function addNewLog($vehicle_id, $log_desc, $log_action, $adminUser)
    {
        $myDate = date('Y-m-d H:i:s');
           $my_insert = "INSERT INTO log_details ( `log_id` , `vehicle_id` ,`log_user`,`log_desc` , `log_action` ,date  ) VALUES (NULL,'".$vehicle_id. "','".$adminUser. "','" .$log_desc. "','" .$log_action. "','" .$myDate."')";
        return $my_insert;
        $result_insert = mysql_query($my_insert) or die("Query ERROR");
        return $result_insert;
    }
  
    private function getVehicleUpdateDate($vehicle_id)
    {
      
        $query_data = "SELECT update_date FROM vehicle WHERE vehicle_id='".$vehicle_id."'" ;
        $result_data = mysql_query($query_data) or die("Query ERROR7");
        $result_value = mysql_fetch_object($result_data);
        return $result_value->update_date;
    }
  
     private function createVehicleObject($row)
    {
        
        $tmp = new VehicleVO();
        $tmp->vehicle_id = $row->vehicle_id;
        $tmp->year = $row->year;       
        $tmp->make = $row->make;
        $tmp->model = $row->model;
		if(isset( $row->make_name))
			$tmp->make_name = $row->make_name;
		if(isset( $row->state_name))
			$tmp->state_name = $row->state_name;
			if(isset( $row->v_state_name))
			$tmp->v_state_name = $row->v_state_name;
		if(isset( $row->model_name))
			$tmp->model_name = $row->model_name;
        $tmp->vin = $row->vin;
        $tmp->mileage = $row->mileage;
        $tmp->engine = $row->engine;
        $tmp->transmission = $row->transmission;
        $tmp->drivetrain_type = $row->drivetrain_type;
        $tmp->title_note = $row->title_note;
        $tmp->wrong = $row->wrong;
        $tmp->great = $row->great;
        $tmp->i_want_to = $row->i_want_to;
        $tmp->bid_price = $row->bid_price;
        $tmp->asking_price = $row->asking_price;
        $tmp->removal_date = $row->removal_date;
        $tmp->first_name = $row->first_name;
        $tmp->email = $row->email;
        $tmp->phone = $row->phone;
        $tmp->cell_phone = $row->cell_phone;
        $tmp->contact_me = $row->contact_me;
        $tmp->located_at = $row->located_at;
        $tmp->paper_work = $row->paper_work;
        $tmp->street_address = $row->street_address;
        $tmp->city = $row->city;
        $tmp->state = $row->state;
        $tmp->zip = $row->zip;
        $tmp->v_street_address = $row->v_street_address;
        $tmp->v_city = $row->v_city;
        $tmp->v_state = $row->v_state;
        $tmp->v_zip = $row->v_zip;
        $tmp->contact_name = $row->contact_name;
        $tmp->contact_phone = $row->contact_phone;
        $tmp->private_note = $row->private_note;
        $tmp->contact_note = $row->contact_note;
        /*$tmp->delivery = $row->delivery;*/
        if(isset($row->company_name))
             $tmp->delivery = $row->company_name;
        $tmp->delivery_note = $row->delivery_note;
        $tmp->delivery_option = $row->delivery_option;
        $tmp->is_watch = (bool) $row->is_watch;
        $tmp->is_read = (bool) $row->is_read;
        $tmp->status = $row->status;
        $tmp->current_action = $row->current_action;
        $tmp->update_date = $row->update_date;
        $tmp->date = $row->date;
		if(isset($row->created_by))
            $tmp->created_by = $row->created_by;
                  
		else
			 $tmp->created_by='';
        $tmp->locked_by = $row->locked_by;
        $tmp->locked_id = $row->locked_id;
        $tmp->assist_by = $row->assist_by;
        $tmp->locked_date = $row->locked_date;
        $tmp->title_name = $row->title_name;
        $tmp->ad_source = $row->ad_source;
        $tmp->color = $row->color;
        $tmp->is_partner = $row->is_partner;
        $tmp->accepted_price = $row->accepted_price;
        $tmp->bussiness_name = $row->bussiness_name;
		
        if(isset($row->status_name))
            $tmp->tow_status_name = $row->status_name;
        else
            $tmp->tow_status_name = "";
        if(isset($row->tow_is_read))
              $tmp->tow_is_read = $row->tow_is_read;
        else
               $tmp->tow_is_read=0;
        if(isset($row->tower_street))
           $tmp->tower_street = $row->tower_street;
        else
           $tmp->tower_street = "";
        if(isset($row->tower_city))
            $tmp->tower_city = $row->tower_city;
        else
             $tmp->tower_city = "";
        if(isset($row->tower_state))
             $tmp->tower_state = $row->tower_state;
        else
             $tmp->tower_state="";
        
         (isset($row->role_id)) ? $tmp->vehicle_role =  $row->role_id: $tmp->vehicle_role="";
        
        
        $tmp->image_collection = self::getAllImageByVehicleID($row->vehicle_id);
        $tmp->messages = self::getAllMessageVehicleID($row->vehicle_id);
        $tmp->privateNote_collection = self::getAllPrivateNote($row->vehicle_id);
       
      
        $tmp->sub_report = self::getSubmissionResult($row->vehicle_id);
        $reminderVO = self::getReminderVO($row->vehicle_id);
		if($reminderVO != 'false')
		{
			$tmp->reminder = $reminderVO;
		}
		else
		{
			$tmp->reminder = new WatchListVO();

		}
				return $tmp;
    }

    public function getSubmissionResult($vehicleID)
    {
          $query_data = "SELECT * FROM submission_tracker WHERE vehicle_id='".$vehicleID."'" ;
            $result_data = mysql_query($query_data) or die("Query ERROR5");
     
            $tmp = new SubmissionVO();
            $row =  mysql_fetch_array($result_data);
            $tmp->vehicle_id = $row['vehicle_id'];
            $tmp->sub_ip = $row['sub_ip'];
            $tmp->sub_platform = $row['sub_platform'];
            $tmp->sub_device = $row['sub_device'];
            $tmp->latitude = $row['gps_latitude'];
            $tmp->longtitude = $row['gps_longtitude'];
            if($row['via_web_buyer'] == 1 ||$row['via_mobile_buyer'] == 1)
            {
                    $tmp->user_type = 'Buyer';
            }
            else
            {
                    $tmp->user_type = 'Seller';
            }
            return $tmp;
    }
  
    public function confirmVehicleLockByID($vehicle_id, $adminName, $adminId)
    {
        $myDate = date('Y-m-d H:i:s');
        $confirm_data = "SELECT is_locked, locked_id FROM vehicle WHERE vehicle_id=".$vehicle_id ;
        $result_data = mysql_query($confirm_data) or die("Query ERROR227");
        $result_value = mysql_fetch_object($result_data);
        if($result_value->is_locked == 0)
        {
            
                $lock_insert = "UPDATE vehicle SET is_locked=1, locked_by='".$adminName."' , locked_id=".$adminId." ,locked_date='".$myDate."' WHERE vehicle_id=".$vehicle_id ;
                $lock_result = mysql_query($lock_insert) or die("Query ERROR1220".$lock_insert );
                if($lock_result)
                    $status = 'open';
            
           
        }
        else
        {
            if($result_value->locked_id != $adminId)
               $status = 'locked';
             else
                $status='open';
        }
        return $status;
    }
     public function checkVehicleLocked($vehicle_id)
    {
       $result_data = mysql_query("SELECT is_locked FROM vehicle WHERE vehicle_id=".$vehicle_id) or die("Query ERROR227");
       $result_value = mysql_fetch_object($result_data);
       if($result_value->is_locked == 0)
           $status = "open";
       else
           $status ="locked";
       return $status;
        
    }
  
    public function unlockVehicleByID($vehicle_id)
    {
        $unlock_insert = "UPDATE vehicle SET is_locked=0, locked_id=0 WHERE vehicle_id =".$vehicle_id ;
        $unlock_result = mysql_query($unlock_insert) or die("Query ERROR1221");
        return $unlock_result;
    }

    public function getAllLockedVehicle()
    {
        $query_lockedData = "SELECT v.vehicle_id,v.locked_by, v.locked_date, m.model_name as model, mk.make FROM vehicle v INNER JOIN vehicle_model m INNER JOIN vehicle_make mk  ON v.model=m.model_id AND v.make = mk.make_id AND v.is_locked=1";
        $result_lockedData = mysql_query($query_lockedData) or die("Query ERROR111");
      
        $arrayCollection = new ArrayCollection();
        $tmps = array();
        $i = 0;
        while ($row =  mysql_fetch_object($result_lockedData))
        {
            $tmps[$i] =    $row;
            $i++;
        }
        $arrayCollection->source = $tmps;
        return $arrayCollection;
    }
  
    public function vehicleShareByEmail($shareVO)
    {
        $myDate = date('Y-m-d H:i:s');
            $key = uniqid();
      
          
      
          
                $relation_insert = "INSERT INTO partner_vehicle_relation ( `vehicle_id` , `partner_id` ,`admin_id`,`activation_status`,
        `date` ) VALUES (".$shareVO->vehicle_id.",".$shareVO->partner_id.",".$shareVO->partner_id. ",'In Force','" .$myDate. "')";
        $relation_insert_1 = mysql_query($relation_insert) or die("Not shared!!! The vehicle ".$shareVO->vehicle_id." , already shared with this partner, Please try with other partners");
      
               
            if($relation_insert_1)
            {
              
            $insert = "UPDATE vehicle SET is_partner=1 WHERE vehicle_id =".$shareVO->vehicle_id ;
            $insert = mysql_query($insert) or die("Query ERROR1".$insert);
      
            $email_insert = "INSERT INTO partner_data_filter ( `vehicle_id` , `partner_id` ,`is_car_details`,
            `is_seller_details` , `is_tow_details` , `is_map_details`, `is_private_details`) VALUES (".$shareVO->vehicle_id.","
            .$shareVO->partner_id.",'".$shareVO->is_car_details. "','" .$shareVO->is_seller_details. "','"
            .$shareVO->is_tow_details. "','" .$shareVO->is_map_details."','" .$shareVO->is_private_details."')";
            $email_insert = mysql_query($email_insert) or die("Query ERROR2".$email_insert);
  
            $url = 'http://www.freecarbids.com/Autogatormediator.php?id='.$key.'&type=part' ;
          
            $email_insert = "INSERT INTO partner_link ( `response_id` ,`vehicle_id` ,`partner_id`,`user_type`,`date_time`,`key`,`note`)
                     VALUES (NULL,".$shareVO->vehicle_id.",".$shareVO->partner_id.",'" .$shareVO->user_type. "','".$myDate. "','" .$key. "',
                     '" .$shareVO->note."')";
                    $result_insert = mysql_query($email_insert) or die("Query ERROR11".$email_insert);
                  
  
           /* $_instance = new TestEmail();
            $_instance->sendEmail($url, 'ShareVehicle', $shareVO->send_to, $shareVO->vehicle_id, 'Partner');*/
        }
      
    }
  
    public function shareByUnknownEmail($shareVO)
    {
        $myDate = date('Y-m-d H:i:s');
        $key = uniqid();
      
        $url = 'http://www.freecarbids.com/Autogatormediator.php?id='.$key.'&type=uknow';
      
        $email_insert = "INSERT INTO unknown_partner_link ( `response_id` ,`vehicle_id` ,`key`,`is_car_details`,
            `is_seller_details` , `is_tow_details` , `is_map_details`, `is_private_details` , `email`, `shared_by`, `note`)
                 VALUES (NULL,".$shareVO->vehicle_id.",'".$key."',".$shareVO->is_car_details. "," .$shareVO->is_seller_details. ","
            .$shareVO->is_tow_details. "," .$shareVO->is_map_details."," .$shareVO->is_private_details.",'".$shareVO->send_to."',"
            .$shareVO->admin_id.",'".$shareVO->note."')";
        $result_insert = mysql_query($email_insert) or die("Query ERROR11".$email_insert);
              

      /*  $_instance = new TestEmail();
        $_instance->sendEmail($url, 'ShareVehicle', $shareVO->send_to, $shareVO->vehicle_id, 'Partner');*/
      
    }
  
    public function getPartnerList()
    {
        $query_part = "SELECT * FROM partner";
        $result_part = mysql_query($query_part) or die("Query ERROR11p1");
        $arrayCollection = new ArrayCollection();
        $tmps[] = '';
        $i = 0;
        while ($row =  mysql_fetch_object($result_part))
        {
            $tmps[$i] =    self::createPartnerList($row);
            $i++;
        }
        $arrayCollection->source = $tmps;
        return $arrayCollection;
    }
  
    private function createPartnerList($row)
    {
        $tmp = new PartnerVO();
            $tmp->partner_id = $row->partner_id;
        $tmp->username = $row->username;
        $tmp->password = $row->password;
        $tmp->f_name = $row->f_name;
        $tmp->l_name = $row->l_name;
        $tmp->company = $row->company;
        $tmp->zip_code = $row->zip_code;
        $tmp->phone_no = $row->phone_no;
        $tmp->email = $row->email;
        $tmp->sec_email = $row->sec_email;
        $tmp->address = $row->address;
        $tmp->country = $row->country;
        $tmp->city = $row->city;
        $tmp->latitude = $row->latitude;
        $tmp->longitude = $row->longitude;
        $tmp->user_type = $row->user_type;
        $tmp->activation_status = $row->activation_status;
        $tmp->who_added = $row->who_added;
        $tmp->date = $row->date;
        return $tmp;
    }
  
    public function getShareDetails($vehicle_key)
    {
	$partner_data = '';        
	$query_bid = mysql_query("SELECT vehicle_id, partner_id FROM partner_link WHERE `key`='".$vehicle_key."'");
        $result_bid = mysql_fetch_array($query_bid);
      
        $query_partner= mysql_query("SELECT email, f_name FROM partner WHERE `partner_id`='".$result_bid['partner_id']."'");
        $result_partner = mysql_fetch_array($query_partner);
     
        $part = new PartnerVO();
        $part->email = $result_partner['email'];
        $part->f_name = $result_partner['f_name'];
        $part->partner_id = $result_bid['partner_id'];

	if($result_bid)
	{
        	$query_filter_1 = "SELECT * FROM partner_data_filter WHERE `vehicle_id`=".$result_bid['vehicle_id']." AND partner_id=".$result_bid['partner_id'];
        	$result_filter_1 = mysql_query($query_filter_1) or die("Error".    $query_filter_1);
        	$result_filter = mysql_fetch_array($result_filter_1);


        	$vehicle_info = self::getVehicleByVehicleID($result_bid['vehicle_id']);
      
        	$partner_data = self::getPartnerVehicleData ($vehicle_info, $result_filter);
        	$partner_data->partner_details = $part;
	}
        return $partner_data;
    }

    public function getUnknownShareDetails($vehicle_key)
    {
       $query_bid = mysql_query("SELECT * FROM unknown_partner_link WHERE `key`='".$vehicle_key."'");
       $result_bid = mysql_fetch_array($query_bid);
      
       $vehicle_info = self::getVehicleByVehicleID($result_bid['vehicle_id']);
      
       $partner_data = self::getPartnerVehicleData ($vehicle_info, $result_bid);
      
       return $partner_data;
    }
   
    private function getPartnerVehicleData ($vehicle_info, $result_filter)
    {
        $tmp = new VehicleVO();
      
        $tmp->title_note = $vehicle_info->title_note;
        $tmp->i_want_to = $vehicle_info->i_want_to;
        $tmp->color = $vehicle_info->color;
        $tmp->status = $vehicle_info->status;
        $tmp->current_action = $vehicle_info->current_action;
           $tmp->date = $vehicle_info->date;
        $tmp->title_name = $vehicle_info->title_name;
        $tmp->ad_source = $vehicle_info->ad_source;
        $tmp->image_collection = $vehicle_info->image_collection;
        $tmp->messages = $vehicle_info->messages;
        $tmp->vehicle_id = $vehicle_info->vehicle_id;
        
       
      
        if($result_filter['is_car_details'] == 1)
        {
          
            $tmp->year = $vehicle_info->year;
            $tmp->make = $vehicle_info->make;
            $tmp->model = $vehicle_info->model;
            $tmp->vin = $vehicle_info->vin;
            $tmp->mileage = $vehicle_info->mileage;
            $tmp->engine = $vehicle_info->engine;
            $tmp->transmission = $vehicle_info->transmission;
            $tmp->drivetrain_type = $vehicle_info->drivetrain_type;
            $tmp->wrong = $vehicle_info->wrong;
            $tmp->great = $vehicle_info->great;
        }
      
        if($result_filter['is_private_details'] == 1)
        {
            $tmp->privateNote_collection = $vehicle_info->privateNote_collection;
        }
      
        if($result_filter['is_map_details'] == 1)
        {
            $tmp->v_street_address = $vehicle_info->v_street_address;
            $tmp->v_city = $vehicle_info->v_city;
            $tmp->v_state = $vehicle_info->v_state;
            $tmp->v_zip = $vehicle_info->v_zip;
        }
      
        if($result_filter['is_tow_details'] == 1)
        {
            $tmp->delivery = $vehicle_info->delivery;
            $tmp->delivery_note = $vehicle_info->delivery_note;
            $tmp->delivery_option = $vehicle_info->delivery_option;
            $tmp->removal_date = $vehicle_info->removal_date;
        }
      
        if($result_filter['is_seller_details'] == 1)
        {
           $tmp->email = $vehicle_info->email;
            $tmp->first_name = $vehicle_info->first_name;
            $tmp->email = $vehicle_info->email;
            $tmp->phone = $vehicle_info->phone;
            $tmp->cell_phone = $vehicle_info->cell_phone;
            $tmp->contact_me = $vehicle_info->contact_me;
            $tmp->located_at = $vehicle_info->located_at;
            $tmp->paper_work = $vehicle_info->paper_work;
            $tmp->street_address = $vehicle_info->street_address;
            $tmp->city = $vehicle_info->city;
            $tmp->state = $vehicle_info->state;
            $tmp->zip = $vehicle_info->zip;
            $tmp->contact_name = $vehicle_info->contact_name;
            $tmp->contact_phone = $vehicle_info->contact_phone;
            $tmp->contact_note = $vehicle_info->contact_note;
            $tmp->bussiness_name = $vehicle_info->bussiness_name;
            $tmp->bid_price = $vehicle_info->bid_price;
            $tmp->asking_price = $vehicle_info->asking_price;
            $tmp->accepted_price = $vehicle_info->accepted_price;
        }
      
        return $tmp;
    }
  
    public function createNewPartner($partnerVO)
    {
        $myDate = date('Y-m-d H:i:s');
        $partnerVO->activation_status = "In Force";
	$checkQuery = "SELECT partner_id FROM `partner` WHERE email='".$partnerVO->email."' OR username='".$partnerVO->username."'";
$checkResult = mysql_query($checkQuery) or die("Query ERROR11".$checkQuery );
$result_check = mysql_fetch_array($checkResult);

if($result_check['partner_id'] == '')
{
	$partner_insert = "INSERT INTO `partner` (`partner_id`, `username`, `password`,
         `f_name`, `l_name`, `company`, `zip_code`, `phone_no`, `email`, `sec_email`, `address`,
         `country`, `city`, `latitude`, `longitude`, `user_type`, `activation_status`,
         `who_added`, `date`) VALUES (NULL, '".$partnerVO->username."','".$partnerVO->password."'
         ,'".$partnerVO->f_name."','".$partnerVO->l_name."','".$partnerVO->company."'
         ,".$partnerVO->zip_code.",".$partnerVO->phone_no.",'".$partnerVO->email."'
         ,'".$partnerVO->sec_email."','".$partnerVO->address."','".$partnerVO->country."'
         ,'".$partnerVO->city."',".$partnerVO->latitude.",".$partnerVO->longitude."
         ,'".$partnerVO->user_type."','".$partnerVO->activation_status."'
         ,'".$partnerVO->who_added."','".$myDate."')";
        $partner_insert_1 = mysql_query($partner_insert) or die("Query ERROR11".$partner_insert);

	return $partner_insert_1;
}  

        
    }

    public function getSubmissionReport($from, $to)
    {
        $query = "SELECT date,SUM( via_web_seller ) AS via_w_seller, SUM( via_mobile_seller ) AS via_m_seller, SUM(via_web_buyer) AS via_w_buyer,
        SUM(via_mobile_buyer) AS via_m_buyer from submission_tracker where date
        BETWEEN '".$from."' AND '".$to."' GROUP BY date";
        $result = mysql_query($query) or die("Query ERROR".$query);
        $arrayCollection = new ArrayCollection();
        $tmps = array();
        $i = 0;
        $tmp_data = new SubmissionVO();
      
        //total var
        $tw_buyer = 0;
        $tm_buyer = 0;
        $tw_seller = 0;
        $tm_seller = 0;
      
        while ($row =  mysql_fetch_object($result))
        {
            $tmp = new SubmissionVO();
            $tmp->date = $row->date;
            $tmp->via_w_buyer = $row->via_w_buyer;
            $tmp->via_m_buyer = $row->via_m_buyer;
            $tmp->via_w_seller = $row->via_w_seller;
            $tmp->via_m_seller = $row->via_m_seller;
            $tmps[$i] = $tmp;
            $tw_buyer += $row->via_w_buyer;
            $tm_buyer += $row->via_m_buyer;
            $tw_seller += $row->via_w_seller;
            $tm_seller += $row->via_m_seller;
            $i++;
        }
        $arrayCollection->source = $tmps;
        $tmp_data->sub_array = $arrayCollection;
        $tmp_data->total_w_buyer = $tw_buyer;
        $tmp_data->total_m_buyer = $tm_buyer;
        $tmp_data->total_w_seller = $tw_seller;
        $tmp_data->total_m_seller = $tm_seller;
        return $tmp_data;
    }

	public function createNewTower($towerVO)
    {
        $myDate = date('Y-m-d H:i:s');
        $partnerVO->activation_status = "In Force";
        $tower_insert = "INSERT INTO `tower` (`tower_id`, `username`, `password`,
         `f_name`, `l_name`, `company`, `zip_code`, `phone_no`, `email`, `sec_email`, `address`,
         `country`, `city`, `latitude`, `longitude`, `user_type`, `activation_status`,
         `who_added`, `date`) VALUES (NULL, '".$towerVO->username."','".$towerVO->password."'
         ,'".$towerVO->f_name."','".$towerVO->l_name."','".$towerVO->company."'
         ,".$towerVO->zip_code.",".$towerVO->phone_no.",'".$towerVO->email."'
         ,'".$towerVO->sec_email."','".$towerVO->address."','".$towerVO->country."'
         ,'".$towerVO->city."',".$towerVO->latitude.",".$towerVO->longitude."
         ,'".$towerVO->user_type."','".$towerVO->activation_status."'
         ,'".$towerVO->who_added."','".$myDate."')";
        $tower_insert_1 = mysql_query($tower_insert) or die("Query ERROR11".$tower_insert);
        return $tower_insert_1;
    }

	public function setWatchList($watchList)
	{
		$myDate = date('Y-m-d H:i:s');
		self::updateVehicleFieldByID($watchList->vehicle_id, 'is_watch',$watchList->isWatch, null, null, $watchList->remind_by);	
		if($watchList->isReminder == 1)
		{
			$query = "INSERT INTO watchlist VALUES(NULL, ".$watchList->vehicle_id.",".$watchList->set_by.",'".$myDate."',
			'".$watchList->to_date."','".$watchList->remind_note."')";
			$result = mysql_query($query) or die("Query ERROR".$query);
			return $result;
		}
		else {
			return true;
		}
	}
	
	public function updateWatchList($watchList)
	{
		$myDate = date('Y-m-d H:i:s');
			
		if($watchList->isReminder == 0)
		{
			$query = "DELETE FROM watchlist WHERE `watch_id` =".$watchList->watch_id;
			$result = mysql_query($query) or die("Query ERROR".$query);
			return $result;
		}
		else {
			self::updateVehicleFieldByID($watchList->vehicle_id, 'is_watch',$watchList->isWatch, null, null, $watchList->remind_by);
			$query = "UPDATE watchlist SET `set_by` = ".$watchList->set_by.", set_date_time = '".$myDate."',`to_date` = '".$watchList->to_date."',
			`remind_note` = '".$watchList->remind_note."' WHERE `watch_id` =".$watchList->watch_id;
			$result = mysql_query($query) or die("Query ERROR".$query);
			return $result;
		}
	}
	
	public function getReminderList()
	{
		$query = "SELECT `watch_id`,`vehicle_id`,`set_by`,`set_date_time`,
		`to_date`,`remind_note`,`first_name` FROM watchlist JOIN administrator on watchlist.set_by= administrator.admin_id";
		$result = mysql_query($query) or die("Query ERROR".$query);
		$arrayCollection = new ArrayCollection();
        $tmps = array();
        $i = 0;
       
        while ($row =  mysql_fetch_object($result))
        {
            $tmp = new WatchListVO();
            $tmp -> watch_id = $row->watch_id;
			$tmp -> vehicle_id = $row->vehicle_id;
			$tmp -> set_by = $row->set_by;
			$tmp -> set_date = $row->set_date_time;
			$tmp -> to_date = $row->to_date;
			$tmp -> remind_note = $row->remind_note;
			$tmp -> remind_by = $row->first_name;
			$tmps[$i] = $tmp;
            $i++;
        }
        $arrayCollection->source = $tmps;
		return $arrayCollection;
	}
	
	public function getReminderVO($vid)
	{
		$query = "SELECT `watch_id`,`vehicle_id`,`set_by`,`set_date_time`,
		`to_date`,`remind_note`,`first_name` FROM watchlist JOIN administrator on watchlist.set_by = administrator.admin_id WHERE watchlist.vehicle_id =".$vid;
		$result = mysql_query($query) or die("Query ERROR".$query);
		
		if(!$result)
		{
			return false;
		}else{
			$tmp = '';
	        while ($row =  mysql_fetch_object($result))
	        {
				$tmp = new WatchListVO();		        
				$tmp -> watch_id = $row->watch_id;
				$tmp -> vehicle_id = $row->vehicle_id;
				$tmp -> set_by = $row->set_by;
				$tmp -> set_date = $row->set_date_time;
				$tmp -> to_date = $row->to_date;
				$tmp -> remind_note = $row->remind_note;
$tmp -> remind_by = $row->first_name;
				return $tmp;
			}
			if($tmp == '')
			 return false;
		}
	}
	
	public function removeWatchlist($watchList)
	{
			
		$myDate = date('Y-m-d H:i:s');	
		if($watchList->isReminder == 0)
		{
			self::updateVehicleFieldByID($watchList->vehicle_id, 'is_watch',$watchList->isWatch, null, null, $watchList->remind_by);
		}
		else {
			self::updateVehicleFieldByID($watchList->vehicle_id, 'is_watch',$watchList->isWatch, null, null, $watchList->remind_by);	
			$query = "DELETE FROM watchlist WHERE `watch_id` =".$watchList->watch_id;
			$result = mysql_query($query) or die("Query ERROR".$query);
			return $result;
		}
	}
}
?>
